package sample.uitl.ui;

import cn.hutool.core.convert.Convert;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {

    static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private static final String EXCEL_XLS_SUFFIX = ".xls";
    private static final String EXCEL_XLSX_SUFFIX = ".xlsx";

    public static List<Map<Integer, Object>> exclToArrayList(String filePath, Integer begenRowNum) {
        //到rowIndex行结束
        Integer rowIndex = null;
        begenRowNum = begenRowNum == null ? 0 : begenRowNum;
        //到columnIndex列结束
        Integer columnIndex = null;
        //定义返回集合
        List<Map<Integer, Object>> returnList = new LinkedList<>();
        //创建文档
        Workbook wb = null;
        //创建sheet页
        Sheet sheet;
        //定义行
        Row row;
        HSSFFormulaEvaluator e1 = null;
        XSSFFormulaEvaluator e2 = null;
        try {
            //读取文件
            InputStream is = new FileInputStream(filePath);
            //根据后缀判断，创建不同版本的对象
            if (filePath.endsWith(EXCEL_XLS_SUFFIX)) {
                wb = new HSSFWorkbook(is);
                e1 = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
            } else if (filePath.endsWith(EXCEL_XLSX_SUFFIX)) {
                wb = new XSSFWorkbook(is);
                e2 = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
            }
            //判空
            if (wb == null) {
                MonologFXUtil.alert("        未识别文件类型!        ");
                throw new Exception("Workbook对象为空！");
            }
            sheet = wb.getSheetAt(0);
            //解析文件总行数、总列数
            int rowNum = rowIndex != null ? rowIndex : sheet.getLastRowNum();
            row = sheet.getRow(0);
            int colNum = columnIndex != null ? columnIndex : row.getLastCellNum();
            //循环列
            for (int colIndex = colNum; colIndex > 0; colIndex--) {
                Cell cell = row.getCell(colIndex);
                if (cell != null && !"".equals(cell.toString())) {
                    colNum = colIndex;
                    break;
                }
            }

            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = begenRowNum; i <= rowNum; i++) {
                row = sheet.getRow(i);
                int j = 0;
                int size = (int) (colNum / .75f) + 1;
                //存储单元格数据
                Map<Integer, Object> cellValue = new LinkedHashMap<>(size);
                if (row == null) continue;
                while (j <= colNum) {
                    Cell cell = row.getCell(j);
                    String value = "";
                    //日期单元格需格式化日期
                    if (cell != null) {
                        if (cell.getCellType() == CellType.NUMERIC) {
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date d = cell.getDateCellValue();
                                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                value = formatter.format(d);
                            } else if (cell.toString().contains("E")) {
                                DecimalFormat nf = new DecimalFormat("0");
                                value = nf.format(cell.getNumericCellValue());
                            } else {
                                value = cell.toString().endsWith(".0") ? cell.toString().replace(".0", "") : cell.toString().trim();
                            }
                        } else if (cell.getCellType() == CellType.FORMULA) {
//                            System.out.println(j);
                            cell = e1==null?e2.evaluateInCell(cell):e1.evaluateInCell(cell);
                            //下面这里出现了个问题，坑啊！cell原类型是Formula，CachedFormulaResultType后是Numeric，因为是计算框，EvaluateInCell后才露出真面目：Error！！！所以需要加下面的判断
                            if (cell.getCellType() != CellType.ERROR)
                                value = String.valueOf(cell.getNumericCellValue());
                            else
                                value = "0";
                        } else {
                            value = cell.toString().trim();
                        }
                    }
                    cellValue.put(j, value);
                    j++;
                }
                returnList.add(cellValue);
            }
            wb.close();
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return returnList;
    }
    public static List<Cell> getCells(Row row,Integer size){
        ArrayList<Cell> cells = new ArrayList<>();
        for (Integer i = 0; i < size; i++) {
            Cell cell = row.getCell(i);
            if (cell == null) cell = row.createCell(i);
            cells.add(cell);
        }
        return cells;
    }

    public static void setRow(Row row, List<String> list) {
        for (Integer i = 0; i < list.size(); i++) {
            Cell cell = row.getCell(i);
            if (cell == null) cell = row.createCell(i);
            if (!"".equals(list.get(i))&&list.get(i).substring(0, 1).equals("=")) {
                cell.setCellFormula(list.get(i).replaceAll("=",""));
            } else {
                cell.setCellValue(list.get(i));
            }
        }
    }
}
